

/** This code constructs Appendix Table J1 **/

global path "/path/to/your/folder"
global MLSdata_path "/path/to/rawMLSdata"
set tracedepth 1
*set trace on
*global path "../../"

set more off

/*** Step 0, Identify the MLS in our sample ***/
/*
use mls_name_abbr using $path/data/full_w_deeds_prepped_new.dta, clear
duplicates drop

levelsof mls_name_abbr, local(mlsnames)
*/

local mlsnames "ARKMLS BBOR BHAM BNAR BROOKLYN CAROLINA CBOR CCMLS CDR CHM CINCY CITRUS DAYTONA ELPASO FSM GAAAR GBRMLS_II GHVMLS GSC HICMLS HIGH IMLS IRES ITHACA JSMLS MARIS MREIS NEFMLS NEOHREX_CRIS NEOHREX_NORMLS NKY NNEREN NNRMLS NORTHSTAR NSBBOR NWLA NWMS PARK PRS RAMC RANWW REALCOMP RIM RIS RMLSFL SAN SANDICOR SEMAR SFAR SIBOR SJSRMLS SUMMIT SWFL SWLA TAR TREND TRIAD TULSA WILM WNC WRIST"

local mlsnames_all "AAR ACTRIS AMELIA ARKMLS ARMLS_II BAREIS BCAAR BCAOR BCAR BCS BHAM BMT BNAR BRIDGE BRIGHT BROOKLYN CADESERT CALAV CAPECOD CAROLINA CBOR CCAR CCMLS CDR CIBR CINCY CITRUS CREN CTEXAS CVR DAYTONA DAYTON DER ELPASO FAY FLAGLER FMLS FSM FTL GAAAR GBRMLS_II GHVMLS GLVAR GNWMLS GOLDEMP GSC GTAOR HAR HBROH HERN HICMLS HIGH HILTONHEAD HLAND HMLMLS IMLS INCLINE IRES ITECH ITHACA JSMLS KVBR LBAR_II LOUISVILLE MAAOR MARIS MAUI MCAR METRO MFR MGCMLS MGMLS MIAAR MLDENVER_RECOLORADO MLDENVER_ROCC MLSGATEWAY MLSLISTINGS MLSLI MLSPIN MRED MREIS MRMLS_CRIS MRMLS_CRM MSX NCRMLS NEFMLS NEOHREX NJMLS_II NKY NNEREN NNRMLS NOBOR NORIS NORTHSTAR NSBBOR NTREIS NWLA NWMLS NWMS NYS_BUFFALO NYS_JEFFERSON_LEWIS NYS_ROCHESTER NYS_SYRACUSE OKEE OMCAR PACMLS PFAOR PPMLS_PIN PRS RAFGC RAMC RANWW RAPCOOP RCMLS REALCOMP REIN RIM RIOGV RIS RMLSFL RMLS SACM SANDICOR SAN SAV SCWMLS_II SEFMIAMI_MIAMI SEFMIAMI_SBBOR SEMAR SFAR SIBOR SIRA SJSRMLS SMART_II STAR SUMMIT SWFL_BONITA SWFL_FLORIDAGULFCOAST SWFL_NABOR SWLA SWMT SYAR TAR TBRMLS TNV TRIAD TULSA ULSTER VICTOR WARDEX WESTAL WESTPENN WMB WRIST"

/*** Step 1, read in the MLS files for  each file ***/

local variables_to_compare closeprice listprice fa_dom fa_closedate fa_originallistdate listagentid buyeragentid listcoagentid buyercoagentid cmas_fips_code fa_propertytype fa_rent_sale_ind

foreach mls_name in `mlsnames_all'  {
unzipfile "$MLSdata_path/tbFA_QuickSearch_`mls_name'.zip"
import delimited "tbFA_QuickSearch_`mls_name'.txt", delimiter("|") clear bindquote(nobind)
rm "tbFA_QuickSearch_`mls_name'.txt"

keep if fa_propertytype == "SF"
keep if fa_rent_sale_ind == "S"
gen sale = closeprice != 0 & closeprice != .

gen sale_price = closeprice if sale ==1 
gen list_price = listprice

gen originallistdate_clean = date(substr(fa_originallistdate,1,10), "YMD")
gen listdate_clean = date(substr(fa_listdate,1,10), "YMD")
drop listdate
gen listdate =  originallistdate_clean
replace listdate = listdate_clean if listdate == .
keep if inrange(year(listdate), 2009, 2014)

gen sale_price_2009 = closeprice if sale == 1 & year(listdate) == 2009
gen sale_price_2014 = closeprice if sale == 1 & year(listdate) == 2014

gen list_price_2009 = listprice if year(listdate) == 2009
gen list_price_2014 = listprice if year(listdate) == 2014


gen closedate_clean = date(substr(fa_closedate,1,10), "YMD")

gen dom_ggp = closedate_clean - listdate

gen sale_30 = sale == 1 & dom_ggp < 30
gen sale_90 = sale == 1 & dom_ggp < 90
gen sale_180 = sale == 1 & dom_ggp < 180
gen sale_365 = sale == 1 & dom_ggp < 365

gen sale_365_2009 = sale_365 if year(listdate) == 2009
gen sale_365_2014 = sale_365 if year(listdate) == 2014	

gen days_on_market = dom_ggp
gen days_to_sale = dom_ggp if sale == 1


gen mls_name_abbr = "`mls_name'"
keep  sale_30 sale_90 sale_180 sale_365 days_on_market days_to_sale sale_price list_price  listagentid buyeragentid listcoagentid buyercoagentid mls_name_abbr cmas_fips_code sale_365_2009 sale_365_2014 list_price_2009 list_price_2014 sale_price_2009 sale_price_2014

collapse sale_30 sale_90 sale_180 sale_365 sale_365_2009 sale_365_2014 days_on_market days_to_sale sale_price list_price sale_price_2009 list_price_2009 (sd) sale_30_sd = sale_30 sale_90_sd = sale_90 sale_180_sd = sale_180 sale_365_sd = sale_365 sale_365_2009_sd = sale_365_2009  sale_365_2014_sd =  sale_365_2014 days_on_market_sd = days_on_market days_to_sale_sd = days_to_sale sale_price_sd = sale_price list_price_sd = list_price sale_price_2009_sd = sale_price_2009  list_price_2009_sd = list_price_2009 (p50) sale_30_p50 = sale_30 sale_90_p50 = sale_90 sale_180_p50 = sale_180 sale_365_p50 = sale_365 days_on_market_p50 = days_on_market days_to_sale_p50 = days_to_sale sale_price_p50 = sale_price list_price_p50 = list_price (count) n = list_price , by(mls_name_abbr cmas_fips_code)

if "`mls_name'" != "AAR" {
append using `file'
}
	
tempfile file
save `file'

}
save "$path/output/mls_summary_data.dta", replace

use "$path/output/mls_summary_data.dta", clear

local mlsnames "ARKMLS BBOR BHAM BNAR BROOKLYN CAROLINA CBOR CCMLS CDR CHM CINCY CITRUS DAYTONA ELPASO FSM GAAAR GBRMLS_II GHVMLS GSC HICMLS HIGH IMLS IRES ITHACA JSMLS MARIS MREIS NEFMLS NEOHREX_CRIS NEOHREX_NORMLS NKY NNEREN NNRMLS NORTHSTAR NSBBOR NWLA NWMS PARK PRS RAMC RANWW REALCOMP RIM RIS RMLSFL SAN SANDICOR SEMAR SFAR SIBOR SJSRMLS SUMMIT SWFL SWLA TAR TREND TRIAD TULSA WILM WNC WRIST"

gen in_sample = 0
foreach mls in `mlsnames' {
replace in_sample = 1 if mls_name_abbr == "`mls'"
}
	

/*** construct County Level counts ***/
preserve
keep cmas_fips_code n in_sample

collapse (sum) n , by(in_sample cmas_fips_code)

/*** drop if less than 1000, less than 1.5 percent of all listings ***/
drop if n < 1000



reshape wide n, i (cmas_fips_code) j(in_sample )
egen n = rowtotal(n0 n1)
gen share = n1/n
replace share = 0 if share == .


save "$path/output/county_fips_list.dta", replace

clear

use "$path/output/county_fips_list.dta", clear

/** Mian Sufi Replication Files ***/
use "$path/data/miansufieconometrica_countylevel.dta", clear
keep fips netwp_h elasticity total medhhinc homevalmed owner educ_col urban
/** Adjust for Miami Dade **/
replace fips = 12025 if fips == 12086
rename fips cmas_fips_code
merge 1:1 cmas_fips_code using "$path/output/county_fips_list.dta"
keep if _merge == 1 | _merge == 3
gen our_sample = (share >0.5 & share != . )*1 + (share <0.5 | share == .)*2

label var homevalmed "Median Home Value"
label var owner "Owner-Occupied Share"
label var educ_col "College Educated Share"
label var urban "Urban Share"
label var medhhinc "Median Household Income"

estpost tabstat  netwp_h elasticity total medhhinc homevalmed owner educ_col urban, c(stat) stat(mean sd count) by(our_sample) nototal 
esttab, /// 
   cells("mean(fmt(%9.2fc)) sd(fmt(%9.2fc)) count(fmt(%6.0fc))") nonumber ///
   nomtitle nonote noobs label collabels( "Mean"  "SD" ) unstack
esttab using "$path/output/summary_table_county.tex", /// 
   cells("mean(fmt(%9.2fc)) sd(fmt(%9.2fc)) count(fmt(%6.0fc))") nonumber ///
   nomtitle nonote noobs label collabels( "Mean"  "SD" "Count" ) unstack replace

/* 2006-2009 House Price Appreciation */
/* 2006-2009 Elasticity */
restore


preserve

collapse (mean) sale_30_mean = sale_30 sale_90_mean = sale_90 sale_180_mean = sale_180 sale_365_mean = sale_365 sale_365_2009_mean = sale_365_2009 sale_365_2014_mean = sale_365_2014 days_on_market_mean = days_on_market days_to_sale_mean = days_to_sale sale_price_mean = sale_price sale_price_2009_mean = sale_price_2009 list_price_2009_mean = list_price_2009  list_price_mean = list_price  sale_30_sd sale_90_sd sale_180_sd sale_365_sd sale_365_2009_sd sale_365_2014_sd days_on_market_sd days_to_sale_sd sale_price_sd list_price_sd sale_price_2009_sd list_price_2009_sd   (rawsum) n = n  [aw = n] , by(in_sample)


gen i = 1
reshape long sale_30 sale_90 sale_180 sale_365 sale_365_2009 sale_365_2014 days_on_market days_to_sale sale_price sale_price_2009  list_price list_price_2009, i(i in_sample) j(stat) string
drop i

gen mean_stat = stat == "_mean"
drop stat
xpose, clear varname

replace _varname = "Listing sold w/in 30 days" if _varname == "sale_30" 
replace _varname = "Listing sold w/in 90 days" if _varname == "sale_90"
replace _varname = "Listing sold w/in 180 days" if _varname == "sale_180"
replace _varname = "Listing sold w/in 365 days" if _varname == "sale_365"
replace _varname = "Listing sold w/in 365 days (2009)" if _varname == "sale_365_2009"
replace _varname = "Listing sold w/in 365 days (2014)" if _varname == "sale_365_2014"
replace _varname = "Days on Market" if _varname == "days_on_market"
replace _varname = "Days to Sale" if _varname == "days_to_sale" 
replace _varname = "Sale Price" if _varname == "sale_price"
replace _varname = "Sale Price (2009)" if _varname == "sale_price_2009"
replace _varname = "List Price" if _varname == "list_price"
replace _varname = "List Price (2009)" if _varname == "list_price_2009" 
replace _varname = "Listing Count" if _varname == "n" 
foreach x in v3 v4 v1 v2 {
	replace `x' = round(`x',.01)
	gen `x'_str = string(`x',"%13.2fc")
}
texsave _varname v3_str v4_str v1_str v2_str using "$path/output/mls_compare_sum_stats.tex", replace
restore

	



